﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Today_YeJi_G]
	(
		@CompanyId VARCHAR(500),
		@Today nchar(10)
		--@StartDate NCHAR(10),
		--@EndDate NCHAR(10)
	)
AS
BEGIN
	DECLARE @strSql VARCHAR(4000)
	SET @strSql='
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY c.Id )AS Row
	,c.Id
	,c.CompanyName
	,ISNULL((Select Sum(SalePrice-CostPrice) From Project Where TypeId=1 and CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IAA
	,ISNULL((Select Sum(SalePrice-CostPrice) From Project Where TypeId=2 and CompanyId=c.Id and Convert(nchar(10),CreateDate,120)='''+@Today+'''),0) As IAB
	,ISNULL((Select Sum(YingShouPrice-ShiShouPrice) From  [Order] Where CompanyId=c.Id and Convert(nchar(10),FinanceRenlingDate,120)='''+@Today+'''),0) As IAC
	,ISNULL((Select Sum(CostPrice) From Project Where CompanyId in ('+@CompanyId+') and Convert(nchar(10),CreateDate,120)='''+@Today+''' and FinanceAudit=1),0) As IAD
	,ISNULL((Select Sum(YingShouPrice) From [Order] Where CompanyId=c.Id and Convert(nchar(10),FinanceRenlingDate,120)='''+@Today+'''),0) As IAE
	,ISNULL((Select Sum(Prices) From Order_PaymentRecord Where SWId=2 and CompanyId=c.Id and Convert(nchar(10),FinanceDate,120)='''+@Today+'''),0) As IAF
	,(0.00) As IAG
			
	From Company c Where ('''+@CompanyId+'''=''0'' Or Id in ('+@CompanyId+'))
	)
	
	Select * From list,(SELECT SUM(IAA) AS IAA_a,SUM(IAB) AS IAB_a,SUM(IAC) AS IAC_a,SUM(IAD) AS IAD_a,SUM(IAE) AS IAE_a,
	SUM(IAF) AS IAF_a,SUM(IAG) AS IAG_a
	
	 FROM list) AS n ORDER BY row'
	 
	 EXEC(@strSql)
End
	RETURN
	
--,ISNULL((Select Sum(IAA) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120) between '''+@StartDate+''' and '''+@EndDate+'''),0) As IAA--新增毛利
	--,ISNULL((Select Sum(IAB) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120) between '''+@StartDate+''' and '''+@EndDate+'''),0) As IAB--续费毛利
	--,ISNULL((Select Sum(IAC) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120) between '''+@StartDate+''' and '''+@EndDate+'''),0) As IAC--新增应收
	--,ISNULL((Select Sum(IAD) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120) between '''+@StartDate+''' and '''+@EndDate+'''),0) As IAD--新增应付
	--,ISNULL((Select Sum(IAE) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120) between '''+@StartDate+''' and '''+@EndDate+'''),0) As IAE--成单金额
	--,ISNULL((Select Sum(IAF) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120) between '''+@StartDate+''' and '''+@EndDate+'''),0) As IAF--尾款收入
	--,ISNULL((Select Sum(IAG) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120) between '''+@StartDate+''' and '''+@EndDate+'''),0) As IAG--非营利性收入
